<?php

/*
 * Copyright (C) 2006-2009 Pham Cong Dinh
 *
 * This file is part of Pone.
 *
 * This is free software; you can redistribute it and/or modify it
 * under the terms of the GNU Lesser General Public License as
 * published by the Free Software Foundation; either version 3 of
 * the License, or (at your option) any later version.
 *
 * This software is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this software; if not, write to the Free
 * Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
 * 02110-1301 USA, or see the FSF site: http://www.fsf.org.
 */

/**
 * The object used for executing a compiled SQL statement and returning the results it produces
 *
 * @category   spica
 * @package    core
 * @subpackage datasource\db\mysql
 * @author     Pham Cong Dinh <pcdinh at phpvietnam dot net>
 * @since      Version 0.1
 * @since      October 19, 2008
 * @copyright  Pham Cong Dinh (http://www.phpvietnam.net)
 * @license    http://www.gnu.org/licenses/lgpl-3.0.txt
 * @version    $Id: PreparedStatement.php 1735 2010-03-29 03:33:20Z pcdinh $
 */

/**
 * MySQL static statement
 */
require_once 'library/spica/core/datasource/db/mysql/Statement.php';

/**
 * Compiled prepared statement interface
 */
require_once 'library/spica/core/datasource/db/PreparedStatement.php';

/**
 * Note: This class can not extend from SpicaMySQLStatement because PHP does not support method overloading
 *
 * # native prepared statements cannot take advantage of the query cache, resulting in lower performance.
 * # native prepared statements cannot execute certains types of queries, like "SHOW TABLES"
 * # native prepared statements don't correctly communicate column lengths for certain other "SHOW" queries, resulting in garbled results.
 * # calling stored procedures multiple times using native prepared statements causes the connection to drop
 *
 * The following statements can be used as prepared statements:
 * CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE,
 * and most SHOW statements. Other statements are not supported in MySQL 5.0.
 */
class SpicaMySQLPreparedStatement extends SpicaMySQLCommonStatement implements SpicaPreparedStatement
{
    /**
     * Database connection
     *
     * @var SpicaMySQLConnection
     */
    protected $_dbConn;

    /**
     * The mysqli_stmt object
     *
     * @var mysqli_stmt
     */
    protected $_stmt;

    /**
     * An array of types assigned to positional or named parameters
     *
     * @example array(':name' => 'string', ':age' => 'int');
     * @var array
     */
    protected $_parameterTypes = array();

    /**
     * Named parameters
     *
     * @example array(':name' = $name);
     * @var array
     */
    protected $_parameters = array();

    /**
     * The last query is executed
     *
     * @var string
     */
    protected $_lastQuery;

    /**
     * The query object that holds information on the rewritten named parameter SQL query
     *
     * @var SpicaNamedParameterQuery
     */
    protected $_namedParameterQuery;

    /**
     * Is statement closed
     *
     * @var bool
     */
    protected $_isClosed = false;

    /**
     * Is current compiled statement a SELECT command
     *
     * @var bool
     */
    protected $_isSelect;

    /**
     * Batch mode parameters
     *
     * @var array
     */
    protected $_batchHolder = array();

    /**
     * Is the prepared statement executing a query with bind variable
     *
     * @var bool
     */
    protected $_namedParameterMode = false;

    /**
     * A map from a generic data type to MySQL data type
     *
     * @var array
     */
    protected $_nativeTypeMap = array(
    'int'      => 'i',
    'string'   => 's', //  VARCHAR2
    'rowid'    => 's',
    'clob'     => 'b',
    'blob'     => 'b',
    'long'     => 'i',
    'long_raw' => 'i',
    'raw'      => 'i',
    'bfile'    => 'b',
    'cfile'    => 'b',
    'cursor'   => 's',
    'double'   => 'd',
    'collection' => 's'
    );

    /**
     * Constructs an object of <code>SpicaMySQLPreparedStatement</code>
     *
     * @see   SpicaMySQLConnection#prepareStatement()
     * @param SpicaMySQLConnection $dbConn
     * @param string $sql
     */
    public function __construct($dbConn, $sql)
    {
        $this->_dbConn    = $dbConn;
        $this->_lastQuery = $sql;
        $this->_stmt      = mysqli_stmt_init($dbConn->getNativeConnection());
    }

    /**
     * Binds named parameters to their values
     *
     * @example
     *   $conn        = SpicaConnectionFactory::getConnection($config);
     *   $sql         = 'INSERT INTO test2 (id, name, age, address) VALUES (:id, :name, :age, :address)';
     *   $pstmt       = $conn->prepareStatement($sql);
     *   $namedParams = array(':name', $name, ':age' => array('value' => $age, 'type' => 'int'), ':address' => $address);
     *   $pstmt->bindParams($namedParams);
     *   $pstmt->execute();
     *
     * @see   http://bugs.php.net/bug.php?id=43178 SP binding is not supported
     * @param array $namedParamValues array of bind values
     */
    public function bindParams($namedParamValues)
    {
        // Reset because they are in use in a single SQL statement
        $this->_parameters     = array();
        $this->_parameterTypes = array();

        // Normalize user assignment
        foreach ($namedParamValues as $name => $params)
        {
            if (false  === is_string($name))
            {
                throw new SpicaDatabaseException('Unable to process bind values because you are mixing numeric parameter placeholders with named parameter ones. Method SpicaMySQLPreparedStatement::bindParams() is used for named parameters only. ');
            }

            if (true   === is_array($params))
            {
                $value = isset($params['value'])?$params['value']:'';
                $type  = isset($params['type'])?$params['type']:'string';
            }
            else
            {
                $value = $params;
                $type  = 'string';
            }

            $this->_parameters[$name]     = $value;
            $this->_parameterTypes[$name] = $type;
        }

        $this->_namedParameterMode = true;
    }

    /**
     * Binds values to prepared SQL statement using a given array of positional/numbered bind
     * variable values as reference for the bind variable order
     *
     * @example
     * 	  $conn   = SpicaConnectionFactory::getConnection($config);
     * 	  $sql    = 'INSERT INTO test2 (id, username) VALUES (?, ?)';
     * 	  $pstmt  = $conn->prepareStatement($sql);
     * 	  $params = array(
     * 	    0 => array('value' => 10, 'type' => 'int'),
     * 	    1 => 'pcdinh10'
     * 	  );
     * 	  $pstmt->bindValues($params);
     * 	  $pstmt->execute();
     * 	  // Free resources
     * 	  $pstmt->close();
     * 	  $conn->close();
     *
     * @param array $value array of bind values. E.x: array($name, array('type' => 'int', 'value' => $age), $address)
     */
    public function bindValues($values)
    {
        // Reset because they are in use in a single SQL statement
        $this->_parameters     = array();
        $this->_parameterTypes = array();

        // Normalize user assignment
        foreach ($values as $pos => $params)
        {
            if (false  === is_int($pos))
            {
                throw new SpicaDatabaseException('Unable to process bind values because you are mixing numeric parameter placeholders with named parameter ones. Method SpicaMySQLPreparedStatement::bindValues() is used for numbered bind parameters only. ');
            }

            if (true   === is_array($params))
            {
                $value = isset($params['value'])?$params['value']:'';
                $type  = isset($params['type'])?$params['type']:'string';
            }
            else
            {
                $type  = 'string';
                $value = $params;
            }

            $this->_parameters[]     = $value;
            $this->_parameterTypes[] = $type;
        }

        $this->_namedParameterMode = false;
    }

    /**
     * Executes the SQL statement in this <code>SpicaMySQLPreparedStatement</code> object,
     * which may be any kind of SQL statement.
     *
     * Some prepared statements return multiple results; the execute method handles these complex statements
     * as well as the simpler form of statements handled by executeQuery and executeUpdate
     *
     * @see    SpicaMySQLPreparedStatement::bindValues()
     * @see    SpicaMySQLPreparedStatement::bindParams()
     * @throws SpicaDatabaseException
     * @return bool true if the SQL statement can return a result set;
     *              false if the SQL statement does not return a result set (INSERT/DELETE/UPDATE)
     */
    public function execute()
    {
        if (true  === $this->_isClosed)
        {
            throw new SpicaDatabaseException('Unable to execute the query because the prepared statement is closed. ');
        }

        $this->_guessQueryType();

        if (false === empty($this->_parameters) && true === $this->_namedParameterMode)
        {
            require_once 'library/spica/core/datasource/db/NamedParameterParser.php';
            $this->_namedParameterQuery = SpicaNamedParameterParser::rewriteSqlStatement($this->_lastQuery);
        }

        if (false === $this->_isSelect)
        {
            $this->executeUpdate();
            return false;
        }

        $this->executeQuery();
        return true;
    }

    /**
     * Executes the given SQL statement, which returns a single SpicaMySQLResultSet object like SELECT, SHOW ...
     *
     * @throws SpicaDatabaseException if a database access error occurs
     * @return SpicaMySQLResultSet
     */
    public function executeQuery()
    {
        try
        {
            $this->_doRealQueryExecution();
            return $this->getResultSet();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL command to retrieve data. ', null, null, null, $ex);
        }
    }

    /**
     * Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement
     * or an SQL statement that returns nothing, such as an SQL DDL statement
     *
     * @throws SpicaDatabaseException if a database access error occurs
     * @return bool
     */
    public function executeUpdate()
    {
        try
        {
            $this->_doRealQueryExecution();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL command to manipulate the data. ', null, null, null, $ex);
        }
    }

    /**
     * Adds a set of parameters to the batch
     *
     */
    public function addBatch()
    {
        $this->_guessQueryType();

        if (true === $this->_isSelect)
        {
            throw new SpicaDatabaseException('Method addBatch() and executeBatch() does not support SQL commands that returns a result set. ');
        }

        $this->_batchHolder['parameters'][]      = $this->_parameters;
        $this->_batchHolder['parameter_types'][] = $this->_parameterTypes;
    }

    /**
     * Submits a batch of commands to the database for execution
     *
     * @throws SpicaDatabaseException if a database-access error occurs, or the adapter does not support batch statements
     * @throws SpicaDatabaseBatchUpdateException
     * @throws BadMethodCallException if no addBatch() call is made before
     * @return array an array of update counts containing one element for each command in the batch
     *               The array is ordered according to the order in which commands were inserted into the batch
     *               Array specs: array('affected_rows', 'last_insert_id')
     */
    public function executeBatch()
    {
        if (true  === $this->_isClosed)
        {
            throw new SpicaDatabaseException('Unable to execute the query because the prepared statement is closed. ');
        }

        if (false === empty($this->_batchHolder['parameters']))
        {
            $parameters     = $this->_batchHolder['parameters'];
            $parameterTypes = $this->_batchHolder['parameter_types'];

            if (false === empty($this->_parameters) && true === $this->_namedParameterMode)
            {
                require_once 'library/spica/core/datasource/db/NamedParameterParser.php';
                $this->_namedParameterQuery = SpicaNamedParameterParser::rewriteSqlStatement($this->_lastQuery);
            }

            $sql = $this->_rewriteQuery();

            $this->_compileStatement($sql);

            // Batch size: number of commands
            $batchSize = count($parameters);

            // Execution results
            $rs = array();

            for ($i = 0; $i < $batchSize; $i++)
            {
                // Refresh parameters
                $this->_parameters     = $parameters[$i];
                $this->_parameterTypes = $parameterTypes[$i];

                // bind parameters for markers
                $this->_bindValuesToCompiledStatement();

                // execute statement
                $success  = mysqli_stmt_execute($this->_stmt);

                if (false === $success)
                {
                    throw new SpicaDatabaseBatchUpdateException('Unable to execute the compiled statement. ', mysqli_stmt_error($this->_stmt), mysqli_stmt_sqlstate($this->_stmt), mysqli_stmt_errno($this->_stmt));
                }

                $rs[] = array(
                  'affected_rows'  => mysqli_stmt_affected_rows($this->_stmt),
                  'last_insert_id' => mysqli_stmt_insert_id($this->_stmt)
                );

                // No need to to call self::closeCursor()
                // Procedural way makes sense here
                mysqli_stmt_free_result($this->_stmt);
                mysqli_stmt_reset($this->_stmt);
            }

            return $rs;
        }

        throw new BadMethodCallException('A batch of commands must be prepared with addBatch() before executeBatch() is invoked. ');
    }

    /**
     * Closes the cursor, allowing the statement to be executed again
     *
     * @return bool
     */
    public function closeCursor()
    {
        if (null !== $this->_stmt)
        {
            mysqli_stmt_free_result($this->_stmt);
            // Resets a prepared statement on client and server to state after prepare.
            // Returns TRUE on success or FALSE on failure.
            return mysqli_stmt_reset($this->_stmt);
        }

        return false;
    }

    /**
     * Fetches result set of multiple rows returned from SQL command as an associative array
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  int $caseMode The value may be one of: null,
     *                       SpicaResultSet::IDENTIFIER_LOWERCASE,
     *                       SpicaResultSet::IDENTIFIER_UPPERCASE,
     *                       SpicaResultSet::IDENTIFIER_MIXEDCASE
     * @return array An empty array is returned when there is no row found
     */
    public function fetchAll($caseMode = null)
    {
        if (true  === $this->_isClosed)
        {
            throw new SpicaDatabaseException('Unable to execute the query because the prepared statement is closed. ');
        }

        if (false === empty($this->_parameters) && true === $this->_namedParameterMode)
        {
            require_once 'library/spica/core/datasource/db/NamedParameterParser.php';
            $this->_namedParameterQuery = SpicaNamedParameterParser::rewriteSqlStatement($this->_lastQuery);
        }

        try
        {
            $this->_doRealQueryExecution();
            $rs = $this->getResultSet($caseMode);
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL command to retrieve data. ', null, null, null, $ex);
        }

        return $rs->getAssociativeArray();
    }

    /**
     * Fetches a single value of the first row and first column
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @return string|null Null value is returned when no row is found
     */
    public function fetchOne()
    {
        $this->_executeReadQuery();

        // mysqli_result
        // If $data is null then there is no result set. See http://vn2.php.net/manual/en/mysqli-stmt.store-result.php
        $data    = mysqli_stmt_result_metadata($this->_stmt);
        if (null === $data || false === $data)
        {
            return null;
        }

        // Fetch the first column name
        $field = mysqli_fetch_field_direct($data, 0);

        /* @var $field stdClass */
        // bind result variables
        mysqli_stmt_bind_result($this->_stmt, ${$field->name});
        // fetch value
        mysqli_stmt_fetch($this->_stmt);
        return ${$field->name};
    }

    /**
     * Fetches a single column from a result set as an associative array
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @return array An empty array is returned when there is no row found
     */
    public function fetchColumn()
    {
        $this->_executeReadQuery();

        // mysqli_result
        // If $data is null then there is no result set. See http://vn2.php.net/manual/en/mysqli-stmt.store-result.php
        $data    = mysqli_stmt_result_metadata($this->_stmt);
        if (null === $data || false === $data)
        {
            return null;
        }

        // Fetch the first column name
        $field = mysqli_fetch_field_direct($data, 0);

        /* @var $field stdClass */
        // bind result variables
        mysqli_stmt_bind_result($this->_stmt, ${$field->name});

        // fetch value
        $rs = array();
        // return null when no more rows/data exists or data truncation occurred
        // return false if any error occurs
        while (mysqli_stmt_fetch($this->_stmt))
        {
            $rs[] = ${$field->name};
        }

        return $rs;
    }

    /**
     * Fetches a single row from result set returned from SQL command as an associative array
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  int $caseMode The value may be one of: null,
     *                       SpicaResultSet::IDENTIFIER_LOWERCASE,
     *                       SpicaResultSet::IDENTIFIER_UPPERCASE,
     *                       SpicaResultSet::IDENTIFIER_MIXEDCASE
     * @return array An empty array is returned when there is no row found
     */
    public function fetchRow($caseMode = null)
    {
        $this->_executeReadQuery();

        // First row only
        mysqli_stmt_data_seek($this->_stmt, 0);

        $row = $this->_bindFieldsToResult();

        if (false === $row)
        {
            return array();
        }

        if (null === $caseMode)
        {
            $caseMode = $this->_dbConn->getSQLIdentifierCase();
        }

        // Returns row data using the variables bound by mysqli_stmt_bind_result()
        // null/true/false
        mysqli_stmt_fetch($this->_stmt);
        $data = array();

        foreach ($row as $fieldName => $value)
        {
            if (SpicaResultSet::IDENTIFIER_LOWERCASE === $caseMode)
            {
                $fieldName = strtolower($fieldName);
            }
            elseif (SpicaResultSet::IDENTIFIER_UPPERCASE === $caseMode)
            {
                $fieldName = strtoupper($fieldName);
            }

            // field name is not a reference anymore
            $data[$fieldName] = $value;
        }

        unset($row);
        return $data;
    }

    /**
     * Retrieves certain set of rows for page navigation.
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  int $pageNumber    The page sequence number
     * @param  int $recordPerPage The number of records that will be displayed on each page
     * @param  int $caseMode The value may be one of: null,
     *                       SpicaResultSet::IDENTIFIER_LOWERCASE,
     *                       SpicaResultSet::IDENTIFIER_UPPERCASE,
     *                       SpicaResultSet::IDENTIFIER_MIXEDCASE
     * @return array
     */
    public function fetchPage($pageNumber = 1, $recordPerPage = 20, $caseMode = null)
    {
        // Calculate the ceiling limit and floor limit
        // Ceiling limit (offset): Starting index of records to fetch from
        $this->_lastQuery = SpicaMySQLCommand::modifyLimitQuery($this->_lastQuery, $recordPerPage, ($pageNumber - 1)*$recordPerPage);
        return $this->fetchAll($caseMode);
    }

    /**
     * Retrieves the certain number of rows counted on a given row sequence number.
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  int    $from The beginning row sequence number that will be retrieved, starting with 0
     * @param  int    $to   The last row sequence number that will be retrieved
     * @param  int $caseMode The value may be one of: null,
     *                       SpicaResultSet::IDENTIFIER_LOWERCASE,
     *                       SpicaResultSet::IDENTIFIER_UPPERCASE,
     *                       SpicaResultSet::IDENTIFIER_MIXEDCASE
     * @return array
     */
    public function fetchRange($from = 0, $to = 20, $caseMode = null)
    {
        if ($from > $to)
        {
            throw new SpicaDatabaseException('The higher sequence row number ($from:'.htmlentities($from).') can not greater than the lower sequence row number ($to:'.htmlentities($to).').');
        }

        // Row at index $to is included
        $this->_lastQuery = SpicaMySQLCommand::modifyLimitQuery($this->_lastQuery, $to - $from + 1, $from);
        return $this->fetchAll($caseMode);
    }

    /**
     * (non-PHPdoc)
     * @see trunk/library/spica/core/datasource/db/mysql/PreparedStatement#insert()
     */
    public function insert($tableName, $data)
    {
        throw new SpicaSQLFeatureNotSupportedException('SpicaMySQLPreparedStatement::insert() is not supported. See SpicaMySQLStatement::insert()');
    }

    /**
     * (non-PHPdoc)
     * @see trunk/library/spica/core/datasource/db/mysql/PreparedStatement#update()
     */
    public function update($tableName, $data, $where)
    {
        throw new SpicaSQLFeatureNotSupportedException('SpicaMySQLPreparedStatement::update() is not supported. See SpicaMySQLStatement::update()');
    }

    /**
     * (non-PHPdoc)
     * @see trunk/library/spica/core/datasource/db/mysql/PreparedStatement#delete()
     */
    public function delete($tableName, $where = array())
    {
        throw new SpicaSQLFeatureNotSupportedException('SpicaMySQLPreparedStatement::delete() is not supported. See SpicaMySQLStatement::delete()');
    }

    /**
     * (non-PHPdoc)
     * @see trunk/library/spica/core/datasource/db/mysql/PreparedStatement#replace()
     */
    public function replace($tableName, $data)
    {
        throw new SpicaSQLFeatureNotSupportedException('SpicaMySQLPreparedStatement::replace() is not supported. See SpicaMySQLStatement::replace()');
    }

    /**
     * Returns result set from a prepared statement; this method should be called only once per result.

     * @throws SpicaDatabaseException if a database access error occurs or
     *         this method is called on a closed <code>SpicaStatement</code>
     * @see    SpicaStatement#getResultSet
     * @see    SpicaMySQLPreparedStatement::_doRealQueryExecution()
     * @see    SpicaMySQLPreparedStatement::executeQuery()
     *
     * @params int $fieldCase The case mode of the field name
     * @return SpicaMySQLPreparedStatementResultSet The current result as a <code>SpicaResultSet</code>
     */
    public function getResultSet($fieldCase = null)
    {
        $case = (null === $fieldCase) ? $this->_dbConn->getSQLIdentifierCase() : $fieldCase;
        return new SpicaMySQLPreparedStatementResultSet($this->_stmt, $this->_isSelect, $case);
    }

    /**
     * Executes a SQL against database server
     *
     * @throws SpicaDatabaseException
     * @return bool
     */
    protected function _doRealQueryExecution()
    {
        $sql = $this->_rewriteQuery();

        try
        {
            $this->_compileStatement($sql);
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute the SQL statement because it can not be compiled. ', null, null, null, $ex);
        }

        $this->_bindValuesToCompiledStatement();

        // execute statement
        $success  = mysqli_stmt_execute($this->_stmt);

        if (false === $success)
        {
            throw new SpicaDatabaseException('Unable to execute the compiled statement. ', mysqli_stmt_error($this->_stmt), mysqli_stmt_sqlstate($this->_stmt), mysqli_stmt_errno($this->_stmt));
        }

        if (true  === $this->_isSelect)
        {
            mysqli_stmt_store_result($this->_stmt);
        }
    }

    /**
     * Executes SELECT commands
     *
     * @throws SpicaDatabaseException
     * @see    SpicaMySQLPreparedStatement::fetchOne(), SpicaMySQLPreparedStatement::fetchColumn()
     */
    protected function _executeReadQuery()
    {
        if (true  === $this->_isClosed)
        {
            throw new SpicaDatabaseException('Unable to execute the query because the prepared statement is closed. ');
        }

        if (false === empty($this->_parameters) && true === $this->_namedParameterMode)
        {
            require_once 'library/spica/core/datasource/db/mysql/SpicaNamedParameterParser.php';
            $this->_namedParameterQuery = SpicaNamedParameterParser::rewriteSqlStatement($this->_lastQuery);
        }

        $sql = $this->_rewriteQuery();

        $success  = mysqli_stmt_prepare($this->_stmt, $sql);

        if (false === $success)
        {
            throw new SpicaDatabaseException('Unable to execute the SQL statement because it can not be compiled. ', null, null, null, $ex);
        }

        $this->_bindValuesToCompiledStatement();

        // execute statement
        $success  = mysqli_stmt_execute($this->_stmt);

        if (false === $success)
        {
            throw new SpicaDatabaseException('Unable to execute the compiled statement. ', mysqli_stmt_error($this->_stmt), mysqli_stmt_sqlstate($this->_stmt), mysqli_stmt_errno($this->_stmt));
        }
    }

    /**
     * Really prepares a SQL statement for execution
     *
     * This method is called internally by SpicaMySQLPreparedStatement#_doRealQueryExecution
     *
     * @throws SpicaDatabaseException
     * @param  string $sql
     */
    protected function _compileStatement($sql)
    {
        try
        {
            $conn = $this->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to create a prepared statement because database connection has been closed. ', null, null, null, $ex);
        }

        $success  = mysqli_stmt_prepare($this->_stmt, $sql);

        if (false === $success)
        {
            throw new SpicaDatabaseException('Unable to compile the query statement. ', mysqli_stmt_error($this->_stmt), mysqli_sqlstate($conn), mysqli_stmt_errno($this->_stmt));
        }
    }

    /**
     * Gets the positional parameter SQL statement (or rewritten named parameter SQL statement if any)
     *
     * @internal Used by SpicaMySQLPreparedStatement::_doRealQueryExecution()
     * @see      SpicaMySQLPreparedStatement::_doRealQueryExecution
     * @return string
     */
    protected function _rewriteQuery()
    {
        if (null !== $this->_namedParameterQuery)
        {
            return $this->_namedParameterQuery->rewrittenQuery;
        }

        return $this->_lastQuery;
    }

    /**
     * Binds values to positional parameter SQL statement or rewritten named parameter one
     *
     * @internal Used by SpicaMySQLPreparedStatement::_doRealQueryExecution()
     * @see      SpicaMySQLPreparedStatement::_doRealQueryExecution
     */
    protected function _bindValuesToCompiledStatement()
    {
        $parameterTypeCount = count($this->_parameterTypes);

        // Nothing to bind
        if (0 === $parameterTypeCount)
        {
            return;
        }

        $typeSeq  = '';

        if (null !== $this->_namedParameterQuery)
        {
            $sql          = $this->_namedParameterQuery->rewrittenQuery;
            $namedParams  = $this->_namedParameterQuery->parameterNames; // :name => pos
            $parameters   = array_flip($namedParams);                    // pos   => :name
            $types        = $this->_parameterTypes;                      // :name => type
            $params       = array();

            for ($i = 0, $length = count($parameters); $i < $length; $i++)
            {
                $typeStr         = $types[$parameters[$i]];
                $typeSeq        .= $typeStr[0];
                $parameters[$i]  = $this->_parameters[$parameters[$i]]; // pos -> value variable
            }
        }
        else
        {
            $sql          = $this->_lastQuery;
            $parameters   = $this->_parameters; // pos -> value variable

            for ($i = 0; $i < $parameterTypeCount; $i++)
            {
                $typeSeq .= $this->_parameterTypes[$i][0]; // first character only
            }
        }

        $bindNames[] = & $this->_stmt;
        $bindNames[] = $typeSeq;

        for ($i = 0; $i < count($parameters); $i++)
        {
            $newBindName  = 'p'.$i;          // $p0, $p1, $p2... (placeholders or markers)
            $$newBindName = $parameters[$i]; // Assign value to a new variable
            $bindNames[]  = &$$newBindName;
        }

        call_user_func_array('mysqli_stmt_bind_param', $bindNames);
    }

    /**
     * Binds field names to result set
     *
     * @return array|false Returns false on empty result set or any error occured
     */
    protected function _bindFieldsToResult()
    {
        mysqli_stmt_store_result($this->_stmt);

        if (0 === mysqli_stmt_num_rows($this->_stmt))
        {
            return false;
        }

        // mysqli_result
        // If $data is null then there is no result set. See http://vn2.php.net/manual/en/mysqli-stmt.store-result.php
        $data = mysqli_stmt_result_metadata($this->_stmt);

        if (null === $data || false === $data)
        {
            return false;
        }

        $row = array();
        // Get field information for all columns
        // $field is an object of stdClass
        $counter = 1;
        while ($field = mysqli_fetch_field($data))
        {
            $row[$field->name] = & ${$counter++};
        }

        call_user_func_array('mysqli_stmt_bind_result', array_merge(array($this->_stmt), $row));
        return $row;
    }

    /**
     * Guesses SQL statement type
     *
     * @see SpicaMySQLPreparedStatement::_isSelect
     * @see SpicaCommonStatement#_guessQueryType()
     */
    protected function _guessQueryType($sql = null)
    {
        // Parameter $sql is not used by intention
        $this->_isSelect = SpicaDatabaseCommand::validateSelect($this->_lastQuery);
    }

    /**
     * Closes the statement actually
     *
     * @param bool $calledExplicitly
     */
    protected function _realClose($calledExplicitly = false)
    {
        if (true === $this->_isClosed)
        {
            return true;
        }

        if ($this->_stmt)
        {
            $result      = mysqli_stmt_close($this->_stmt);
            $this->_stmt = null;
            return $result;
        }

        return false;
    }

    /**
     * Destroys the current object of <code>SpicaMySQLPreparedStatement</code>
     */
    public function __destruct()
    {
        $this->_currentResultSet = null;
        $this->_isClosed         = true;
    }
}

/**
 * A table of data representing a database result set, which is usually generated by
 * executing a statement that queries the database
 *
 * @category   spica
 * @package    core
 * @subpackage datasource\db\mysql
 * @author     Pham Cong Dinh <pcdinh at phpvietnam dot net>
 * @since      Version 0.1
 * @since      October 27, 2008
 * @copyright  Pham Cong Dinh (http://www.phpvietnam.net)
 * @license    http://www.gnu.org/licenses/lgpl-3.0.txt
 * @version    $Id: PreparedStatement.php 1735 2010-03-29 03:33:20Z pcdinh $
 */
class SpicaMySQLPreparedStatementResultSet implements SpicaResultSet
{
    /**
     * Compiled and executed prepared statement object
     *
     * @var mysqli_stmt
     */
    protected $_stmt;

    /**
     * Is this result available to retrieve from?
     *
     * If it is not, this result set contains an error and carry a message to explain this status
     *
     * @var bool
     */
    protected $_isAvailable = false;

    /**
     * A flag to indicate if the result set is closed
     *
     * @var bool
     */
    protected $_isClosed = false;

    /**
     * The current row that the cursor points to
     *
     * @var int
     */
    protected $_currentCursor = 0;

    /**
     * The total rows in the result set
     *
     * @var int
     */
    protected $_rowCount = null;

    /**
     * The total fields in the result set
     *
     * @var int
     */
    protected $_fieldCount = null;

    /**
     * An associative array that corresponds to the currently fetched row or NULL if there are no more rows
     *
     * @var array|null
     */
    protected $_currentRow = null;

    /**
     * Is this result set be produced by a SELECT or SHOW statement?
     *
     * @var bool
     */
    protected $_isSelect = true;

    /**
     * All fields names should be in lowercase, upper case or mixed case
     *
     * @var int
     */
    protected $_fieldCase;

    /**
     * Constructs an object of <code>SpicaMySQLPreparedStatementResultSet</code>
     *
     * The parameter $result can be an instance of mysqli_result if the statement is SELECT, SHOW, EXPLAIN
     * query or be a FALSE value if the statement contains something wrong or be a TRUE value
     * if the statement is INSERT, UPDATE, DELETE, REPLACE query
     *
     * @param mysqli_stmt|null $statement MySQL prepared statement object which is compiled and executed
     * @param bool $readQuery  Is the query for read purpose (SELECT, SHOW)
     * @param int  $fieldCase  How field name is retrieved: upper or lower case
     */
    public function __construct($statement, $readQuery, $fieldCase)
    {
        if ($statement instanceof mysqli_stmt)
        {
            $this->_stmt        = $statement;
            $this->_isAvailable = true;
        }
        else
        {
            $this->_isAvailable = (bool) $statement;
        }

        $this->_isSelect  = $readQuery;
        $this->_fieldCase = $fieldCase;
    }

    /**
     * Retrieves all rows in the current result set as a <code>SpicaRowList</code> object
     *
     * @return SpicaRowList
     */
    public function getRowList()
    {
        include_once 'library/spica/core/utils/ContainerUtils.php';
        return new SpicaRowList($this->getAssociativeArray());
    }

    /**
     * Retrieves XML representation of all rows in the current result set
     *
     * @return string
     */
    public function getXml()
    {
        include_once 'library/spica/core/utils/FormatUtils.php';
        return SpicaFormatUtils::arrayToXml($this->getAssociativeArray());
    }

    /**
     * Gets a JSON representation of rows contained in the result set
     *
     * @return string
     */
    public function getJson()
    {
        return json_encode($this->getAssociativeArray());
    }

    /**
     * Fetches all rows from the result set as an associative array
     *
     * This method always returns an array. Field names returned by this function are case-sensitive
     *
     * @return array
     */
    public function getAssociativeArray()
    {
        $this->_checkAvailability();

        if (null === $this->_stmt)
        {
            throw new BadMethodCallException('Method getAssociativeArray() requires SELECT statement. ');
        }

        $row = $this->_bindFieldsToResult();

        if (true === empty($row))
        {
            return array();
        }

        // Field name case
        $case = null;
        
        if (SpicaResultSet::IDENTIFIER_LOWERCASE === $this->_fieldCase)
        {
            $case = CASE_LOWER;
        }
        elseif (SpicaResultSet::IDENTIFIER_UPPERCASE === $this->_fieldCase)
        {
            $case = CASE_UPPER;
        }

        // Returns row data using the variables bound by mysqli_stmt_bind_result()
        // null/true/false
        while ($this->_stmt->fetch())
        {
            // Clean all reference in $row
            foreach ($row as $fieldName => $value)
            {
                $temp[$fieldName] = $value;
            }

            if (null !== $case)
            {
                $temp = array_change_key_case($temp, $case);
            }

            $rs[] = $temp;
        }

        return $rs;
    }

    /**
     * Determines how many result rows were found by the preceding query
     *
     * @return int The number of result rows.
     */
    public function rowCount()
    {
        $this->_checkAvailability();
        if (null === $this->_stmt)
        {
            throw new BadMethodCallException('Method rowCount() requires SELECT statement. ');
        }

        if (null === $this->_rowCount)
        {
            // Return the number of rows in statements result set
            $this->_rowCount = mysqli_stmt_num_rows($this->_stmt);
        }

        return $this->_rowCount;
    }

    /**
     * Moves the cursor to the given row number in this <code>SpicaResultSet</code> object
     *
     * @throws InvalidArgumentException, SpicaDatabaseException
     * @param  int $offset
     * @return bool
     */
    public function absolute($offset)
    {
        if (false === is_int($offset))
        {
            throw new InvalidArgumentException('Method absolute() requires its first parameter to be an integer. ');
        }

        if ($offset > $this->rowCount())
        {
            return false;
        }

        try
        {
            $this->_checkAvailability();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to move the cursor the offset '.$offset.' because the result set is not available or closed', null, null, null, $ex);
        }

        if (null === $this->_stmt)
        {
            throw new BadMethodCallException('Method absolute() requires SELECT statement. ');
        }

        mysqli_stmt_data_seek($this->_stmt, $offset - 1);

        $row = $this->_bindFieldsToResult();
        // Returns row data using the variables bound by mysqli_stmt_bind_result()
        // null/true/false
        mysqli_stmt_fetch($this->_stmt);

        $data = array();

        foreach ($row as $fieldName => $value)
        {
            if (SpicaResultSet::IDENTIFIER_LOWERCASE === $this->_fieldCase)
            {
                $fieldName = strtolower($fieldName);
            }

            // field name is not a reference anymore
            $data[$fieldName] = $value;
        }

        unset($row);

        $this->_currentCursor = $offset;
        $this->_currentRow    = $data;
        return true;
    }

    /**
     * Moves the cursor to the first row in this <code>SpicaResultSet</code> object
     *
     * @throws InvalidArgumentException, SpicaDatabaseException
     * @return bool
     */
    public function first()
    {
        return $this->absolute(1);
    }

    /**
     * Retrieves the current row number
     *
     * The first row is number 1, the second number 2, and so on.
     *
     * @see    SpicaResultSet#currentRowNumber
     * @return int
     */
    public function currentRowNumber()
    {
        return $this->_currentCursor;
    }

    /**
     * Returns row at the current cursor
     *
     * @see    SpicaResultSet#absolute()
     * @return array
     */
    public function currentRow()
    {
        return $this->_currentRow;
    }

    /**
     * Tests if it is possible to retrieve data from a <code>SpicaResultSet</code> object
     *
     * A <code>SpicaResultSet</code> object is not available to extract data
     * when this object contains an error
     *
     * @return bool
     */
    public function isAvailable()
    {
        return $this->_isAvailable;
    }

    /**
     * Retrieves whether this <code>SpicaResultSet</code> object has been closed
     *
     * A <code>SpicaStatement</code> is closed if the method close has been called on it,
     * or if it is automatically closed
     *
     * @return bool true if this <code>SpicaStatement</code> object is closed;
     *              false if it is still open
     */
    public function isClosed()
    {
        return $this->_isClosed;
    }

    /**
     * Retrieves whether the cursor is on the first row of this
     * <code>SpicaResultSet</code> object
     *
     * @return bool
     */
    public function isFirst()
    {
        return (1 === $this->_currentCursor);
    }

    /**
     * Retrieves whether the cursor is on the last row of this <code>SpicaResultSet</code> object
     *
     * @return bool
     */
    public function isLast()
    {
        return ($this->rowCount() === $this->_currentCursor);
    }

    /**
     * Moves the cursor to the last row in this <code>SpicaResultSet</code> object
     *
     * @return bool
     */
    public function last()
    {
        $this->_currentCursor = $this->_rowCount;
        return $this->absolute(($this->_rowCount));
    }

    /**
     * Moves the cursor to the previous row in this <code>SpicaResultSet</code> object
     *
     * @throws InvalidArgumentException, SpicaDatabaseException
     * @return bool
     */
    public function previous()
    {
        if (0 >= $this->_currentCursor)
        {
            return false;
        }

        $cursor = $this->_currentCursor - 1;

        if (0 > $cursor)
        {
            return false;
        }

        return $this->absolute($cursor);
    }

    /**
     * Moves the cursor forward one row from its current position
     *
     * @throws InvalidArgumentException, SpicaDatabaseException
     * @return bool
     */
    public function next()
    {
        if (0 === $this->rowCount())
        {
            return false;
        }

        if ($this->_rowCount < $this->_currentCursor + 1)
        {
            return false;
        }

        $this->absolute($this->_currentCursor + 1);

        if (null === $this->_currentRow)
        {
            return false;
        }

        $this->_currentCursor++;
        return true;
    }

    /**
     * This method returns data about the columns returned as part of the
     * result set as a <code>SpicaResultSetMetaData</code> instance.
     *
     * @exception SpicaDatabaseException If an error occurs.
     * @return SpicaResultSetMetaData The <code>SpicaResultSetMetaData</code> instance for this result set.
     */
    public function getMetaData()
    {
        $this->_checkAvailability();
        return new SpicaMySQLPreparedStatementResultSetMetaData($this->_stmt, $this->_isSelect, $this->_fieldCase);
    }

    /**
     * Releases this <code>SpicaResultSet</code> object's database
     * and resources immediately instead of waiting for this to happen when it is automatically closed
     */
    public function close()
    {
        $this->_realClose(true);
    }

    /**
     * Releases this <code>SpicaResultSet</code> object's database
     *
     * @param bool $calledExplicitly
     */
    protected function _realClose($calledExplicitly = false)
    {
        // No need to call mysqli_stmt_close($this->_stmt);
        $this->_currentCursor = 0;
        $this->_currentRow    = null;
        $this->_rowCount      = null;
        $this->_fieldCount    = null;
        $this->_isClosed      = true;
        $this->_stmt          = null;
        $this->_isAvailable   = false;
    }

    /**
     * Tests the availability of the result set to extract data from.
     *
     * @throws SpicaDatabaseException
     */
    protected function _checkAvailability()
    {
        if (true  === $this->_isClosed)
        {
            throw new SpicaDatabaseException('Unable to extract data from a closed result set. ');
        }

        if (false === $this->_isAvailable)
        {
            throw new SpicaDatabaseException('Unable to extract data from a result set that is produced by an errornous statement. ');
        }
    }

    /**
     * Binds field names to result set
     *
     * @return array
     */
    protected function _bindFieldsToResult()
    {
        mysqli_stmt_store_result($this->_stmt);

        if (0 === mysqli_stmt_num_rows($this->_stmt))
        {
            return array();
        }

        // mysqli_result
        // If $data is null then there is no result set. See http://vn2.php.net/manual/en/mysqli-stmt.store-result.php
        $data = mysqli_stmt_result_metadata($this->_stmt);
        $row  = array();
        $rs   = array();

        // Get field information for all columns
        // $field is an object of stdClass
        $counter = 1;
        while ($field = mysqli_fetch_field($data))
        {
            $row[$field->name] = & ${$counter++};
        }

        call_user_func_array('mysqli_stmt_bind_result', array_merge(array($this->_stmt), $row));
        return $row;
    }

    /**
     * Destroys the current <code>SpicaMySQLPreparedStatementResultSet</code>
     */
    public function __destruct()
    {

    }
}

/**
 * The SpicaResultSetMetaData interface creates an object that can be used to find out
 * about the types and properties of the columns in a SpicaResultSet.
 *
 * @category   spica
 * @package    core
 * @subpackage datasource
 * @author     Pham Cong Dinh <pcdinh at phpvietnam dot net>
 * @since      Version 0.3
 * @since      December 12, 2008
 * @copyright  Pham Cong Dinh (http://www.phpvietnam.net)
 * @license    http://www.gnu.org/licenses/lgpl-3.0.txt
 * @version    $Id: PreparedStatement.php 1735 2010-03-29 03:33:20Z pcdinh $
 */
class SpicaMySQLPreparedStatementResultSetMetaData implements SpicaResultSetMetaData
{
    /**
     * Is this result set be produced by a SELECT or SHOW statement?
     *
     * @var bool
     */
    protected $_isSelect = true;

    /**
     * All fields names should be in lowercase, upper case or mixed case
     *
     * @var int
     */
    protected $_fieldCase;

    /**
     * The total fields in the result set
     *
     * @var int
     */
    protected $_fieldCount;

    /**
     * Names of columns
     *
     * @var array
     */
    protected $_columns;

    /**
     * Information on fields
     *
     * @var array an array of stdClass
     */
    protected $_columnInfo;

    /**
     * Is column names populated?
     *
     * @var bool
     */
    protected $_columnPopulated = false;

    /**
     * Compiled and executed prepared statement object
     *
     * @var mysqli_stmt
     */
    protected $_stmt;

    /**
     * Constructs an object of <code>SpicaMySQLResultSetMetaData</code>
     *
     * The parameter $result can be an instance of mysqli_result if the
     * statement is SELECT, SHOW, EXPLAIN query or be a FALSE value if the statement
     * contains something wrong or be a TRUE value if the statement is INSERT, UPDATE, DELETE, REPLACE query
     *
     * @param mysqli_stmt|bool $statement MySQL prepared statement object which is compiled and executed
     * @param bool $readQuery  Is the query for read purpose (SELECT, SHOW)
     * @param int  $fieldCase  How field name is retrieved: upper or lower case
     */
    public function __construct($stmt, $readQuery, $fieldCase)
    {
        if ($stmt instanceof mysqli_stmt)
        {
            $this->_stmt = $stmt;
        }

        $this->_fieldCase = $fieldCase;
        $this->_isSelect  = $readQuery;
    }

    /**
     * This method returns the number of columns in the result set.
     *
     * @exception SpicaDatabaseException If an error occurs.
     * @return int The number of columns in the result set.
     */
    public function getColumnCount()
    {
        if (null === $this->_stmt)
        {
            return false;
        }

        // Verbose code for performance
        if (null !== $this->_fieldCount)
        {
            return $this->_fieldCount;
        }

        $this->_fieldCount = $this->_stmt->field_count;
        return $this->_fieldCount;
    }

    /**
     * This method returns the name of the specified column.
     *
     * @exception SpicaDatabaseException If an error occurs.
     * @param  int $column The index of the column to return the name of.
     * @return string|false The name of the column.
     */
    public function getColumnName($column)
    {
        if (null === $this->_stmt)
        {
            return false;
        }

        if (false === $this->_columnPopulated)
        {
            $this->_populateColumnNames();
        }

        if (null !== $this->_columns && isset($this->_columns[$column]))
        {
            return $this->_columns[$column];
        }

        return false;
    }

    /**
     * This method returns names of columns by order.
     *
     * @exception SpicaDatabaseException If an error occurs.
     * @return array|false The ordered names of columns.
     */
    public function getColumnNames()
    {
        if (null === $this->_stmt)
        {
            return false;
        }

        if (false === $this->_columnPopulated)
        {
            $this->_populateColumnNames();
        }

        if (null !== $this->_columns)
        {
            return $this->_columns;
        }

        return false;
    }

    /**
     * This method returns the name of the schema that contains the specified
     * column.
     *
     * @exception SpicaDatabaseException If an error occurs.
     * @param  int $column The index of the column to check the schema name for.
     * @return string The name of the schema that contains the column.
     */
    public function getSchemaName($column)
    {
        return $this->getTableName($column);
    }

    /**
     * This method returns the name of the table containing the specified
     * column.
     *
     * @exception SpicaDatabaseException If an error occurs.
     * @param  int $column The index of the column to check the table name for.
     * @return string|false The name of the table containing the column.
     */
    public function getTableName($column)
    {
        if (null === $this->_stmt)
        {
            return false;
        }

        if (null === $this->_columnInfo)
        {
            $rs = $this->_stmt->result_metadata();
            // Get field information for all columns
            $this->_columnInfo = $rs->fetch_fields();
        }

        if (true === isset($this->_columnInfo[0]))
        {
            return $this->_columnInfo[0]->orgtable;
        }

        return false;
    }

    /**
     * Fetches columns
     */
    protected function _populateColumnNames()
    {
        if (null === $this->_columnInfo)
        {
            $rs = $this->_stmt->result_metadata();
            // Get field information for all columns
            $this->_columnInfo = $rs->fetch_fields();
        }

        $cols = array();
        foreach ($this->_columnInfo as $info)
        {
            $cols[] = $info->name;
        }

        $this->_columns = $cols;
        $this->_columnPopulated = true;
    }
}

?>